﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using DiscoveryLogic.Analytics.DataObjects;
using DiscoveryLogic.Common.Utilities;

namespace DiscoveryLogic.Analytics.BusinessLogic.Importers
{
    public class GrantImportUtil
    {
        public static List<GrantDO> GetGrantsByProjectIDs(List<int> projIDs)
        {
            string connStr = ConfigurationManager.ConnectionStrings["NihProject"].ConnectionString;
            List<GrantDO> grants=new List<GrantDO>();
            SqlConnection conn=new SqlConnection(connStr);
            try
            {
                conn.Open();
                string sql =
                @"select distinct 
a.*,
b.ACTIVITY_CODE,
b.APPL_TYPE_CODE, 
b.FY,
b.SUPPORT_YEAR from 
dbo.Project a inner join dbo.Appl b
on a.projectid=b.project_id where a.ProjectID={0}";
                SqlCommand cmd = conn.CreateCommand();
                foreach(int projID in projIDs)
                {
                    cmd.CommandText = string.Format(
                        sql, projID);
                    SqlDataReader reader = cmd.ExecuteReader();
                    if(reader.Read())
                    {
                        GrantDO grant = new GrantDO();
                        grant.ProjectID = reader["ProjectID"] == DBNull.Value ? 0 : int.Parse(reader["ProjectID"].ToString());
                        grant.Abstract = reader["Abstract"] == DBNull.Value ? string.Empty : reader["Abstract"].ToString();
                        grant.AccessionNum = reader["AccessionNum"] == DBNull.Value ? 0 : int.Parse(reader["AccessionNum"].ToString());
                        grant.ActivityCode = reader["ACTIVITY_CODE"].ToString();
                        grant.ApplTypeCode = int.Parse(reader["APPL_TYPE_CODE"].ToString());
                        grant.PhsAdminOrg = reader["AdminPHSOrgCode"].ToString();
                        grant.SerialNum = int.Parse(reader["SerialNum"].ToString());
                        grant.SupportYear = int.Parse(reader["SUPPORT_YEAR"].ToString());
                        grant.GrantNumber = string.Format(
                            "{0} {1}{2} {3}-{4}",
                            grant.ApplTypeCode, grant.ActivityCode, grant.PhsAdminOrg,
                            Padding(grant.SerialNum, 6), Padding(grant.SupportYear, 2));
                        grant.Title = reader["Title"] == DBNull.Value ? "" : reader["Title"].ToString();
                        grants.Add(grant);
                    }
                    reader.Close();
                }
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return grants;
        }

        public static List<GrantDO> GetGrantsByGrantNumbers(List<string> grantNums)
        {
            string connStr = ConfigurationManager.ConnectionStrings["NihProject"].ConnectionString;
            List<GrantDO> grants = new List<GrantDO>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                string sql =
                @"select distinct 
a.*,
b.ACTIVITY_CODE,
b.APPL_TYPE_CODE, 
b.FY,
b.SUPPORT_YEAR from 
dbo.Project a inner join dbo.Appl b
on a.projectid=b.project_id where 
a.MajorActivityCode='{0}' and 
a.AdminPHSOrgCode='{1}' and 
a.SerialNum={2}";
                SqlCommand cmd = conn.CreateCommand();
                foreach (string grantNum in grantNums)
                {
                    string majorActivityCode = string.Empty;
                    string phsOrgCode = string.Empty;
                    int serial = 0;
                    bool canParse = ParseGrantNumber(grantNum, ref majorActivityCode, ref phsOrgCode, ref serial);
                    if(!canParse)
                        continue;
                    
                    cmd.CommandText = string.Format(
                        sql, majorActivityCode, phsOrgCode, serial);
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        GrantDO grant = new GrantDO();
                        grant.ProjectID = reader["ProjectID"] == DBNull.Value ? 0 : int.Parse(reader["ProjectID"].ToString());
                        grant.Abstract = reader["Abstract"] == DBNull.Value ? string.Empty : reader["Abstract"].ToString();
                        grant.AccessionNum = reader["AccessionNum"] == DBNull.Value ? 0 : int.Parse(reader["AccessionNum"].ToString());
                        grant.ActivityCode = reader["ACTIVITY_CODE"].ToString();
                        grant.ApplTypeCode = int.Parse(reader["APPL_TYPE_CODE"].ToString());
                        grant.PhsAdminOrg = reader["AdminPHSOrgCode"].ToString();
                        grant.SerialNum = int.Parse(reader["SerialNum"].ToString());
                        grant.SupportYear =reader["SUPPORT_YEAR"]==DBNull.Value? 0: int.Parse(reader["SUPPORT_YEAR"].ToString());
                        grant.GrantNumber = string.Format(
                            "{0} {1}{2} {3}-{4}",
                            grant.ApplTypeCode, grant.ActivityCode, grant.PhsAdminOrg,
                            Padding(grant.SerialNum, 6), Padding(grant.SupportYear, 2));
                        grant.Title = reader["Title"] == DBNull.Value ? "" : reader["Title"].ToString();
                        grants.Add(grant);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
            return grants;
        }

        private static bool ParseGrantNumber(string grantNum, 
            ref string majorActivityCode,
            ref string phsAdminOrgCode,
            ref int serialNum)
        {
            Regex grantNumRegex=new Regex(@"^\d?\s?(\w)\d{2}(\w{2})(\d{6})",RegexOptions.IgnoreCase);
            Match grantNumMatch = grantNumRegex.Match(grantNum);
            if(grantNumMatch.Success)
            {
                majorActivityCode = grantNumMatch.Groups[1].Value;
                phsAdminOrgCode = grantNumMatch.Groups[2].Value;
                string serialNumStr = grantNumMatch.Groups[3].Value;
                serialNum = int.Parse(serialNumStr);
                return true;
            }
            return false;
        }

        private static string Padding(int num, int digits)
        {
            string numstr = num.ToString();
            while (numstr.Length < digits)
                numstr = "0" + numstr;
            return numstr;
        }
    }
}
